/*
 * Transaction DAO.java
 *
 * Created on February 13, 2007
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

/**
 *
 * @author craig marshall
 */
package finalintex2web;
import java.util.*;
import java.io.*;
import java.sql.*;
/**
 * Version 1.0
 *
 * The data base access object for transactions
 */
public class TransactionDAO {
    
    
    ///////////////////////////////////////
    ///   Singleton pattern
    
    private static TransactionDAO instance = null;
    
    /** Creates a new instance of TransactionDAO */
    private TransactionDAO() {
    }
    
    /**
     * 
     * @return 
     */
    public static synchronized TransactionDAO getInstance() {
        if (instance == null) {
            instance = new TransactionDAO();
        }
        return instance;
    }
    
    
    /////////////////////////////////
    ///   CREATE
     
    /**
     *  creates new Transaction
     *  @return TransactionBO
     */
    public TransactionBO create() {
        String id = GUID.generate();
        TransactionBO tran = new TransactionBO(id);
        Cache c = Cache.getInstance();
        c.put(tran.getId(), tran);
        return tran;
    }
    
    
    /////////////////////////////////////
    ///   READ
     /**
     *  reads object from database
     *  @return TransactionBO
     *  @param  id is the object id
     */
    public synchronized TransactionBO read(String id) throws DataException,ConnectionPoolException {
        if(Cache.getInstance().containsKey(id)){
            TransactionBO tran = (TransactionBO)Cache.getInstance().get(id);
            return tran;
        }
        
        try {
            Connection conn = ConnectionPool.getInstance().get();
            TransactionBO tran = (TransactionBO)read(id,conn);
            ConnectionPool.getInstance().release(conn);
            return tran;
        }catch (SQLException e) {
            // rollback
            throw new DataException("SQL error from read 1", e);
        }catch (ConnectionPoolException e){
            throw new ConnectionPoolException("Connection pool error in read 1", e);
        }
        
     
    }
    
    /** 
     *  This is a package method that is called by the public read (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection TransactionDBOs or SQLExceptions.
     *  @param id is the object id 
     *  @param conn in the database connection
     */
    public synchronized TransactionBO read(String id, Connection conn) throws SQLException, DataException, ConnectionPoolException {
            if(Cache.getInstance().containsKey(id)){
            TransactionBO tran = (TransactionBO)Cache.getInstance().get(id);
            return tran;
            } 
                
           try{        
           PreparedStatement stmt = conn.prepareStatement("SELECT * FROM Transactions WHERE id=?");
           stmt.setString(1,id);
           ResultSet rs =  stmt.executeQuery();
           
           ConnectionPool.getInstance().release(conn);
            
            if (rs.next()) {
                TransactionBO tran = new TransactionBO(id);
                tran.setStatus(rs.getString("status"));
                tran.setType(rs.getString("transtype"));
                tran.setReturntransaction(rs.getString("returntrans"));
                tran.setCustomer(CustomerDAO.getInstance().read(rs.getString("customer")));
                tran.setStore(StoreDAO.getInstance().read(rs.getString("store")));
                tran.setEmployee(EmployeeDAO.getInstance().read(rs.getString("employee")));
                 //get txls
                PreparedStatement stmt2 = conn.prepareStatement("SELECT txl.id FROM transactions t, transactionline txl WHERE t.id=?");
                stmt2.setString(1,id);
                ResultSet rs2 =  stmt2.executeQuery();
                ArrayList<TransactionLineBO> translines = new ArrayList<TransactionLineBO>();
                while (rs2.next()){
                    TransactionLineBO tl = TransactionLineDAO.getInstance().read(rs.getString("id"));
                    translines.add(tl);
                    tl.setIndb(true);
                }
                tran.setTransactionlines(translines);
                
                if(rs.getString("payment") != null)
                    tran.setPayment(PaymentDAO.getInstance().read(rs.getString("payment")));
                else{
                    tran.setPayment(PaymentDAO.getInstance().create()); 
                    tran.getPayment().setAmount(0);
                }
                Cache.getInstance().put(id,tran);      
                tran.setIndb(true);
                stmt.close();
                return tran;
               

            }else{
                throw new DataException("TransactionBO was not found in the database.");
            }
           
            }catch (ConnectionPoolException e){
                throw new ConnectionPoolException("Could not retrieve a connection from the database", e);
            
            }catch (SQLException e){
                throw new DataException("SQL error", e);
            }
            
    }
        
    
    
    //////////////////////////////////
    ///   UPDATE
    
    /** 
     * This is the public save method.  It is what is called when
     * the user (controller) code wants to save or update an TransactionDBO
     * into the database.
     * @param tran is the object to be saved to teh database
     */
    public synchronized void save(TransactionBO tran) throws DataException,ConnectionPoolException {
        
        
        try {
            Connection conn = ConnectionPool.getInstance().get();
            save(tran,conn);
            ConnectionPool.getInstance().release(conn);
        }catch (SQLException e) {
            //rollback
            throw new DataException("Could not retrieve record for id=" + tran.getId(), e);
            
            }catch (ConnectionPoolException e){
            throw new ConnectionPoolException("Could not retrieve record for id=" + e);
        }
        
        // release the connection back to the pool
        return;
    }
    
    /** 
     *  This is a package method that is called by the public save (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection TransactionDBOs or SQLExceptions.
     *
     *  By having other DAOs call this save method (rather than update or
     *  insert below, each DAO in a chained save (like the TransactionDAO calling
     *  MembershipDAO calling InterestDAO save chain) can independently decide
     *  whether to udpate or insert the BO it's saving.  That's why I made
     *  update and insert private rather than package level -- if you call
     *  them directly from another DAO, this DAO can't decide whether it's
     *  TransactionDBO needs to be inserted or updated.
     */
     synchronized void save(TransactionBO tran, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        // check the dirty flag in the TransactionDBO.  if it is dirty, 
        // run update or insert
        if (tran.isDirty()) {
            if (tran.isIndb()) {
                update(tran, conn);
            }else{
                insert(tran, conn);
            }
            tran.setDirty(false);
        
        
        Cache.getInstance().touch(tran.getId());
     
        }
        
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     */
    private synchronized void update(TransactionBO tran, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        PreparedStatement stmt = conn.prepareStatement("UPDATE Transaction SET status = ?, customer = ?, employee = ?, store = ?,transdate = ?, transtype =?, returntrans = ? WHERE id=? ");
        stmt.setString(1,tran.getStatus());
        stmt.setString(2,tran.getCustomer().getId());
        stmt.setString(3,tran.getEmployee().getId());
        stmt.setString(4,tran.getStore().getId());
        stmt.setString(5,tran.getDate());
        stmt.setString(6,tran.getType());
        stmt.setString(7,tran.getReturntransaction());
        stmt.setString(8,tran.getId());
        stmt.execute();
        stmt.close();
        ConnectionPool.getInstance().release(conn);
    }
     
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL insert statement and make it more readable.  But
     * logically, it's really just a part of save.
     */
    private synchronized void insert(TransactionBO tran, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        // do the insert SQL statement
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO transactions (status,customer,employee,store,transdate,transtype,returntrans,id) VALUES ( ?,?,?,?,?,?,?,?)");
        stmt.setString(1,tran.getStatus());
        stmt.setString(2,tran.getCustomer().getId());
        stmt.setString(3,tran.getEmployee().getId());
        stmt.setString(4,tran.getStore().getId());
        stmt.setString(5,tran.getDate());
        stmt.setString(6,tran.getType());
        stmt.setString(7,tran.getReturntransaction());
        stmt.setString(8,tran.getId());
        stmt.execute();
        stmt.close();
        // tell the TransactionDBO that it's now in the db (so we call update next time not insert)
        tran.setIndb(true);
        ConnectionPool.getInstance().release(conn);
    }
    
    
    ////////////////////////////////////
    ///   DELETE
    // We are not implementing this feature in this class to help preserve data intergity.
}
